/**
 * 
 */
package org.compiere.report;

import java.io.File;
import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Properties;
import java.util.logging.Level;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.CellFormat;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.NumberFormats;
import jxl.write.WritableCell;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import org.adempiere.exceptions.AdempiereException;
import org.compiere.acct.Doc;
import org.compiere.apps.ADialog;
import org.compiere.model.MAcctSchema;
import org.compiere.model.MAttachmentEntry;
import org.compiere.model.MBPartner;
import org.compiere.model.X_hrm_salaryemployee;
import org.compiere.process.GeneratePayrollList;
import org.compiere.process.ProcessInfo;
import org.compiere.process.ProcessInfoParameter;
import org.compiere.process.SvrProcess;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;
import org.joda.time.DateTime;

import extend.org.compiere.hrm.ExcelCell;
import extend.org.compiere.utils.Util;

/**
 * @author V.Sokolov
 *
 */
public class ReportPayroll extends SvrProcess {
	
	/** Current context		*/
	private Properties m_ctx;
	/**	 */
	private ProcessInfo pi;
	/** */
	private String path = "";
	/** */
	private Timestamp dateFrom = null;

	/* 
	 */
	@Override
	protected void prepare() {
		
		log.info("");
		m_ctx = Env.getCtx();
		pi = getProcessInfo();
		
		ProcessInfoParameter[] para = getParameter();
		for (int i = 0; i < para.length; i++)	{
			String name = para[i].getParameterName();
			if (name == null); 
				//
			else if (name.compareTo("DateFrom") == 0)
				dateFrom = (Timestamp) para[i].getParameter();
			else
			{
				log.log(Level.INFO, "Unknown Parameter: " + name);
			}
		}
		
		if(path.isEmpty())
			path = Util.localFilePath;
		else
			path = path + System.getProperty("file.separator");
		
		if(dateFrom == null)
			dateFrom = new Timestamp(new DateTime().getMillis());
	}

	/* 
	 */
	@Override
	protected String doIt() throws Exception {
		
		// Types of cells for
		Label label;
		Number number;
		// There are checks for a specified pattern
		String templateName = "SamplePayroll";
		
		//
		MAttachmentEntry entry = Util.getAttachment(pi, m_ctx, templateName);
		if(entry == null)
			throw new AdempiereException(Msg.translate(m_ctx, "NotFoundTemplate")+" "+templateName);
				
		// We define a path to generate
		String fileExtension = entry.getName().substring(entry.getName().lastIndexOf("."),entry.getName().length());
		StringBuffer fullPath = new StringBuffer(path); 
		fullPath.append("Payroll").append(fileExtension);
				
		// 
		File templateCopy = new File(fullPath.toString());
		File temporaryFile = entry.getFile(Util.localFilePath+templateName);
		Workbook tableBook = null;
		WritableWorkbook copy = null;
		WritableSheet sheet = null;
		 try {
			 tableBook = Workbook.getWorkbook(temporaryFile);
			 copy = Workbook.createWorkbook(templateCopy, tableBook);
			 sheet = copy.getSheet(0);
		} catch (Exception e) {
		  	ADialog.error(999, null, "Error tableBook. " + e.getMessage());
		   	return "Error tableBook.";
		}

		ExcelCell cellStart =  Util.getCellStart(tableBook,">>");
		
		// Standart style cell
		WritableFont standartFont = new WritableFont (WritableFont.ARIAL, 8, WritableFont.NO_BOLD);
		WritableCellFormat numberDouble = new WritableCellFormat(standartFont, NumberFormats.THOUSANDS_FLOAT);
		numberDouble.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
		numberDouble.setAlignment(Alignment.RIGHT);
		numberDouble.setVerticalAlignment(VerticalAlignment.CENTRE);
		numberDouble.setWrap(true);
		// Standart style cell
		WritableFont totalFont = new WritableFont (WritableFont.ARIAL, 8, WritableFont.BOLD);
		WritableCellFormat numberTotal = new WritableCellFormat(totalFont, NumberFormats.THOUSANDS_FLOAT);
		numberTotal.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
		numberTotal.setAlignment(Alignment.RIGHT);
		numberTotal.setVerticalAlignment(VerticalAlignment.CENTRE);
		numberTotal.setWrap(true);
		// Integer
		WritableCellFormat numberInt = new WritableCellFormat(standartFont);
		numberInt.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
		numberInt.setAlignment(Alignment.RIGHT);
		numberInt.setVerticalAlignment(VerticalAlignment.CENTRE);
		numberInt.setWrap(true);
		// Department style cell
		WritableFont fontDepartment = new WritableFont (WritableFont.TIMES, 11, WritableFont.BOLD);
		WritableCellFormat cellDepartment = new WritableCellFormat(fontDepartment);
		cellDepartment.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
		cellDepartment.setAlignment(Alignment.LEFT);
		cellDepartment.setVerticalAlignment(VerticalAlignment.CENTRE);
		cellDepartment.setBackground(Colour.LIGHT_GREEN);
		// Department style cell
		WritableFont fontTotal = new WritableFont (WritableFont.TIMES, 11, WritableFont.BOLD);
		WritableCellFormat cellTotal = new WritableCellFormat(fontTotal);
		cellTotal.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
		cellTotal.setAlignment(Alignment.CENTRE);
		cellTotal.setVerticalAlignment(VerticalAlignment.CENTRE);
		// BPartner style cell
		WritableFont fontBP = new WritableFont (WritableFont.ARIAL, 9, WritableFont.NO_BOLD);
		WritableCellFormat labelStyle = new WritableCellFormat(fontBP);
		labelStyle.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
		labelStyle.setAlignment(Alignment.CENTRE);
		labelStyle.setVerticalAlignment(VerticalAlignment.CENTRE);
		labelStyle.setWrap(true);
		
		Object[] param = new Object[]{Util.getMonthName(new Timestamp(dateFrom.getTime()), Env.getAD_Language(getCtx()), false), 
				  Integer.toString(new org.joda.time.DateTime(dateFrom.getTime()).getYear())};
		WritableCell readCell = sheet.getWritableCell(1, 1);
		CellFormat readFormat = readCell.getCellFormat();
		label = new Label(1, 1, sheet.getCell(1, 1).getContents()+MessageFormat.format(" за {0} месяц {1} года", param), readFormat);
		sheet.addCell(label);
		sheet.setRowView(1, 600);
		
		//
		PayrollList[] lines =  getValues();
		
		//--> Total sum
		double totalAccrued  = 0.;
		double compensationTrip = 0.;
		double compensationVacation = 0.;
		double materialAid = 0.;
		double medicareAccrued = 0.;
		double amountInverse = 0.;
		double amountDirect = 0.;
		double amountIllness = 0.;
		double amountVacation = 0.;
		double amountBonus = 0.;
		double totalDeduction = 0.;
		double amountLoan = 0.;
		double IPN = 0.;
		double medicareDeduction = 0.;
		double OPV = 0.;
		double inter = 0.;
		double checkOff = 0.;
		double totalAmount = 0.;
		double endBalance = 0.;
		double CNALOG = 0.;
		double COTCH = 0.;
		double taxExemption = 0.;
		double salary = 0.;
		double workDays = 0.;
		double workHours = 0.;
		double begBalance = 0.;
		//--<
		
		MBPartner bp = null;
		int col = cellStart.getC(); // offset by the number of first columns
		int row = cellStart.getR();
		int dId = 0; // Departmnet ID Merge cells
		int dep = 0;
		int num = 1;
		int i = 0;
		int cntRow = 0;
		int depRow = 0;
		for(int j = row; j < (row+lines.length); j++){
			
			bp = new MBPartner(m_ctx, lines[i].getBPartner_ID(), get_TrxName());
			
			if(dId != lines[i].getDepartment_ID()){
				dId = lines[i].getDepartment_ID();
				sheet.insertRow(j+dep);
				sheet.mergeCells(0, j+dep, 32, j+dep);
				sheet.setRowView(j+dep, 500);
				label = new Label(0, j+dep, lines[i].getDepName(), cellDepartment); 
				sheet.addCell(label);
				dep++;
			} 
			
			MAcctSchema as = MAcctSchema.get(m_ctx, Env.getContextAsInt(m_ctx, "$C_AcctSchema_ID"));
			LinkedHashMap<String, BigDecimal> saldo =  getSaldo(GeneratePayrollList.getAccountSalary(as, Doc.ACCTTYPE_HRMSalary).getAccount_ID(), bp.getC_BPartner_ID());
			
			// #
	    	number = new Number(cellStart.getC(), j+dep, num, labelStyle); 
	    	sheet.addCell(number);
	    	// table number
	    	label = new Label(col+1, j+dep, Integer.toString(bp.gethrm_tableNumber()), labelStyle); 
	    	sheet.addCell(label);
			// initials
			label = new Label(col+2, j+dep, lines[i].getName(), labelStyle); 
			sheet.addCell(label);
			// Job
			label = new Label(col+3, j+dep, lines[i].getJobName(), labelStyle); 
			sheet.addCell(label);
			// Salary
			salary = salary + lines[i].getSalary();
			number = new Number(col+4, j+dep, lines[i].getSalary(), numberDouble);
			sheet.addCell(number);
			// Work Days
			workDays = workDays + lines[i].getWorkDays();
			number = new Number(col+5, j+dep, lines[i].getWorkDays(), numberInt);
			sheet.addCell(number);
			// Work Hours
			workHours = workHours + lines[i].getWorkHours();
			number = new Number(col+6, j+dep, lines[i].getWorkHours(), numberInt);
			sheet.addCell(number);
			// Beginning balance
			begBalance = begBalance + saldo.get("saldo1").doubleValue();
			number = new Number(col+7, j+dep, saldo.get("saldo1").doubleValue(), numberDouble);
			sheet.addCell(number);
			
			//-->> Accrued amount
			// Total Accrued
			totalAccrued = totalAccrued + lines[i].getTotalAccrued();
			number = new Number(col+8, j+dep, lines[i].getTotalAccrued(), numberDouble);
			sheet.addCell(number);
			// Compensation Trip
			compensationTrip = compensationTrip + lines[i].getCompensationTrip();
			number = new Number(col+9, j+dep, lines[i].getCompensationTrip(), numberDouble);
			sheet.addCell(number);
			// Compensation Vacation
			compensationVacation = compensationVacation + lines[i].getCompensationVacation();
			number = new Number(col+10, j+dep, lines[i].getCompensationVacation(), numberDouble);
			sheet.addCell(number);
			// Material Aid
			materialAid = materialAid + (lines[i].getMaterialAid()+lines[i].getAidVacation());
			number = new Number(col+11, j+dep, lines[i].getMaterialAid()+lines[i].getAidVacation(), numberDouble);
			sheet.addCell(number);
			// Medicare
			medicareAccrued = medicareAccrued + lines[i].getMedicareAccrued();
			number = new Number(col+12, j+dep, lines[i].getMedicareAccrued(), numberDouble);
			sheet.addCell(number);
			// Amount Inverse
			amountInverse = amountInverse + lines[i].getAmountInverse();
			number = new Number(col+13, j+dep, lines[i].getAmountInverse(), numberDouble);
			sheet.addCell(number);
			// Amount Direct
			amountDirect = amountDirect + lines[i].getAmountDirect();
			number = new Number(col+14, j+dep, lines[i].getAmountDirect(), numberDouble);
			sheet.addCell(number);
			// Amount Illness
			amountIllness = amountIllness + lines[i].getAmountIllness();
			number = new Number(col+15, j+dep, lines[i].getAmountIllness(), numberDouble);
			sheet.addCell(number);
			// Amount Vacation
			amountVacation = amountVacation + lines[i].getAmountVacation();
			number = new Number(col+16, j+dep, lines[i].getAmountVacation(), numberDouble);
			sheet.addCell(number);
			// Amount Bonus
			amountBonus = amountBonus + lines[i].getAmountBonus();
			number = new Number(col+17, j+dep, lines[i].getAmountBonus(), numberDouble);
			sheet.addCell(number);

			//-->> Deduction amount
			// Total Deduction
			totalDeduction = totalDeduction + lines[i].getTotalDeduction();
			number = new Number(col+18, j+dep, lines[i].getTotalDeduction(), numberDouble);
			sheet.addCell(number);
			// Loan
			amountLoan = amountLoan + lines[i].getAmountLoan();
			number = new Number(col+19, j+dep, lines[i].getAmountLoan(), numberDouble);
			sheet.addCell(number);
			// IPN
			IPN = IPN + lines[i].getIPN();
			number = new Number(col+20, j+dep, lines[i].getIPN(), numberDouble);
			sheet.addCell(number);
			// Medicare deduction
			medicareDeduction = medicareDeduction + lines[i].getMedicareDeduction();
			number = new Number(col+21, j+dep, lines[i].getMedicareDeduction(), numberDouble);
			sheet.addCell(number);
			// OPV
			OPV = OPV + lines[i].getOPV();
			number = new Number(col+22, j+dep, lines[i].getOPV(), numberDouble);
			sheet.addCell(number);
			// Inter
			inter =inter + lines[i].getInter();
			number = new Number(col+23, j+dep, lines[i].getInter(), numberDouble);
			sheet.addCell(number);
			// Исп. лист
			checkOff = checkOff + lines[i].getCheckOff();
			number = new Number(col+24, j+dep, lines[i].getCheckOff(), numberDouble);
			sheet.addCell(number);
			// Result
			totalAmount = totalAmount + lines[i].getTotalAmount();
			number = new Number(col+25, j+dep, lines[i].getTotalAmount(), numberDouble);
			sheet.addCell(number);
			// BANK
			number = new Number(col+26, j+dep, lines[i].getTotalAmount(), numberDouble);
			sheet.addCell(number);
			// End Balance
			endBalance = endBalance + saldo.get("saldo2").doubleValue();
			number = new Number(col+27, j+dep, saldo.get("saldo2").doubleValue(), numberDouble);
			sheet.addCell(number);
			// Всего отчислений
			CNALOG = CNALOG + lines[i].getCNALOG();
			COTCH = COTCH + lines[i].getCOTCH();
			number = new Number(col+28, j+dep, lines[i].getCNALOG()+lines[i].getCOTCH(), numberDouble);
			sheet.addCell(number);
			// CNALOG
			number = new Number(col+29, j+dep, lines[i].getCNALOG(), numberDouble);
			sheet.addCell(number);
			// COTCH
			number = new Number(col+30, j+dep, lines[i].getCOTCH(), numberDouble);
			sheet.addCell(number);
			// Налоговые льготы
			taxExemption = taxExemption + lines[i].getTaxExemption();
			number = new Number(col+31, j+dep, lines[i].getTaxExemption(), numberDouble);
			sheet.addCell(number);
			number = new Number(col+32, j+dep, lines[i].getTaxExemption(), numberDouble);
			sheet.addCell(number);
			
			i++;
			num++;
			cntRow = j;
			depRow = dep;
		}
		
		cntRow++;
		
		//-->> TOTAL
		sheet.mergeCells(col, cntRow+depRow, col+3, cntRow+depRow);
		label = new Label(col, cntRow+depRow, "ИТОГО", cellTotal);
		sheet.addCell(label);
		
		number = new Number(col+4, cntRow+depRow, salary, numberTotal);
		sheet.addCell(number);
		// Work Days
		number = new Number(col+5, cntRow+depRow, workDays, numberInt);
		sheet.addCell(number);
		// Work Hours
		number = new Number(col+6, cntRow+depRow, workHours, numberInt);
		sheet.addCell(number);
		// Beginning balance
		number = new Number(col+7, cntRow+depRow, begBalance, numberTotal);
		sheet.addCell(number);
		
		number = new Number(col+8, cntRow+depRow, totalAccrued, numberTotal);
		sheet.addCell(number);
		// Compensation Trip
		number = new Number(col+9, cntRow+depRow, compensationTrip, numberTotal);
		sheet.addCell(number);
		// Compensation Vacation
		number = new Number(col+10, cntRow+depRow, compensationVacation, numberTotal);
		sheet.addCell(number);
		// Material Aid
		number = new Number(col+11, cntRow+depRow, materialAid, numberTotal);
		sheet.addCell(number);
		// Medicare
		number = new Number(col+12, cntRow+depRow, medicareAccrued, numberTotal);
		sheet.addCell(number);
		// Amount Inverse
		number = new Number(col+13, cntRow+depRow, amountInverse, numberTotal);
		sheet.addCell(number);
		// Amount Direct
		number = new Number(col+14, cntRow+depRow, amountDirect, numberTotal);
		sheet.addCell(number);
		// Amount Illness
		number = new Number(col+15, cntRow+depRow, amountIllness, numberTotal);
		sheet.addCell(number);
		// Amount Vacation
		number = new Number(col+16, cntRow+depRow, amountVacation, numberTotal);
		sheet.addCell(number);
		// Amount Bonus
		number = new Number(col+17, cntRow+depRow, amountBonus, numberTotal);
		sheet.addCell(number);

		//-->> Deduction amount
		// Total Deduction
		number = new Number(col+18, cntRow+depRow, totalDeduction, numberTotal);
		sheet.addCell(number);
		// Loan
		number = new Number(col+19, cntRow+depRow, amountLoan, numberTotal);
		sheet.addCell(number);
		// IPN
		number = new Number(col+20, cntRow+depRow, IPN, numberTotal);
		sheet.addCell(number);
		// Medicare deduction
		number = new Number(col+21, cntRow+depRow, medicareDeduction, numberTotal);
		sheet.addCell(number);
		// OPV
		number = new Number(col+22, cntRow+depRow, OPV, numberTotal);
		sheet.addCell(number);
		// Inter
		number = new Number(col+23, cntRow+depRow, inter, numberTotal);
		sheet.addCell(number);
		// Исп. лист
		number = new Number(col+24, cntRow+depRow, checkOff, numberTotal);
		sheet.addCell(number);
		// Result
		number = new Number(col+25, cntRow+depRow, totalAmount, numberTotal);
		sheet.addCell(number);
		// BANK
		number = new Number(col+26, cntRow+depRow, totalAmount, numberTotal);
		sheet.addCell(number);
		// End Balance
		number = new Number(col+27, cntRow+depRow, endBalance, numberTotal);
		sheet.addCell(number);
		// Всего отчислений
		number = new Number(col+28, cntRow+depRow, CNALOG+COTCH, numberTotal);
		sheet.addCell(number);
		// CNALOG
		number = new Number(col+29, cntRow+depRow, CNALOG, numberTotal);
		sheet.addCell(number);
		// COTCH
		number = new Number(col+30, cntRow+depRow, COTCH, numberTotal);
		sheet.addCell(number);
		// Налоговые льготы
		number = new Number(col+31, cntRow+depRow, taxExemption, numberTotal);
		sheet.addCell(number);
		number = new Number(col+32, cntRow+depRow, taxExemption, numberTotal);
		sheet.addCell(number);
		//--<<
		
		
		copy.write();
		copy.close();
		tableBook.close();
		temporaryFile.delete();
			       
		// Open the file with the application associated
		String fileName = fullPath.toString();
		Util.launchFile(new File(fileName));
		
		return Msg.translate(m_ctx, "Success");
	}
	
	private PayrollList[] getValues(){
		
		List<PayrollList> list = new ArrayList<PayrollList>();
		PayrollList values = new PayrollList();
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;		
		try
		{
			pstmt = DB.prepareStatement(getSql(),null);
			rs = pstmt.executeQuery();	
					
			while (rs.next ())
			{
				values = new PayrollList();
				values.setMethod(rs.getString("method"));
				values.setBPartner_ID(rs.getInt("c_bpartner_id"));
				values.setName(rs.getString("name"));
				values.setDepartment_ID(rs.getInt("department_id"));
				values.setDepName(rs.getString("depname"));
				values.setJobName(rs.getString("jobname"));
				values.setSalary(rs.getDouble("salary"));
				values.setWorkDays(rs.getInt("workdays"));
				values.setWorkHours(rs.getInt("workhours"));
				
				//-->> Accrued amount
				values.setTotalAccrued(rs.getDouble("totalsum"));
				values.setCompensationTrip(rs.getDouble("btrip"));
				values.setCompensationVacation(rs.getDouble("review"));
				values.setAidVacation(rs.getDouble("aidvacation"));
				values.setMaterialAid(rs.getDouble("materialaid"));
				values.setMedicareAccrued(rs.getDouble("medicare"));
				if(values.getMethod().toLowerCase().indexOf("обрат") != -1
						|| values.getMethod().equals(X_hrm_salaryemployee.HRM_SALARYTYPE_Inverse))						
					values.setAmountInverse(rs.getDouble("amount"));
				else 					
					values.setAmountDirect(rs.getDouble("amount"));								
				values.setAmountIllness(rs.getDouble("illness"));
				values.setAmountVacation(rs.getDouble("vacation"));
				values.setAmountBonus(rs.getDouble("bonus"));

				// -->> Deduction amount
				values.setTotalDeduction(rs.getDouble("totaldeduction"));
				values.setAmountLoan(rs.getDouble("loan"));
				values.setIPN(rs.getDouble("ipn"));
				values.setMedicareDeduction(rs.getDouble("medicare_amount"));
				values.setOPV(rs.getDouble("opv"));
				values.setInter(rs.getDouble("inter"));
				values.setCheckOff(rs.getDouble("checkoff"));
				values.setTotalAmount(rs.getDouble("result"));
				values.setCNALOG(rs.getDouble("cnalog"));
				values.setCOTCH(rs.getDouble("cotch"));
				
				values.setTaxExemption(rs.getDouble("relief"));
				list.add(values);
			}
		}
		catch (SQLException e)
		{
			log.log(Level.SEVERE, "product", e);
		}
		finally
		{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}	
		
		return list.toArray(new PayrollList[list.size()]);
	}
	
	private String getSql(){
		
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT BP.NAME, R.HR_DEPARTMENT_ID AS DEPARTMENT_ID, HR_DEPARTMENT.NAME AS DEPNAME, HR_JOB.NAME AS JOBNAME, T.* FROM HRM_SALARYSUM_V T \n");
		sql.append("LEFT JOIN C_BPARTNER BP ON BP.C_BPARTNER_ID=T.C_BPARTNER_ID \n");
		sql.append("LEFT JOIN HR_EMPLOYEE R ON R.C_BPARTNER_ID=T.C_BPARTNER_ID \n");
		sql.append("    	  AND R.ENDDATE = (SELECT MAX(H.ENDDATE) FROM HR_EMPLOYEE H WHERE H.C_BPARTNER_ID=R.C_BPARTNER_ID) \n");
		sql.append("LEFT JOIN AD_TREENODEDEP ON AD_TREENODEDEP.NODE_ID=R.HR_DEPARTMENT_ID \n");
		sql.append("LEFT JOIN HR_JOB ON HR_JOB.HR_JOB_ID=R.HR_JOB_ID \n");
		sql.append("LEFT JOIN HR_DEPARTMENT ON HR_DEPARTMENT.HR_DEPARTMENT_ID=R.HR_DEPARTMENT_ID \n");
		sql.append("WHERE T.DATEFROM BETWEEN DATE_TRUNC('MONTH',"+DB.TO_DATE(dateFrom, true)+") AND DATE_TRUNC('MONTH',"+DB.TO_DATE(dateFrom, true)+") + '1 MON'::INTERVAL - '1 DAY'::INTERVAL \n");
		sql.append("ORDER BY AD_TREENODEDEP.PARENT_ID ASC, AD_TREENODEDEP.SEQNO, HR_JOB.C_JOBCATEGORY_ID, R.C_BPARTNER_ID");
		
		return sql.toString();
	}
	
	private LinkedHashMap<String, BigDecimal> getSaldo(int accountID, int C_BPartner_ID){
		
		LinkedHashMap<String, BigDecimal> result = new LinkedHashMap<String, BigDecimal>();
		result.put("saldo1", new BigDecimal(0));
		result.put("saldo2", new BigDecimal(0));
		PreparedStatement pstmt = null;
		ResultSet rs = null;		
				
		try
		{
			pstmt = DB.prepareStatement(getSqlSaldo(accountID, C_BPartner_ID),null);
			rs = pstmt.executeQuery();	
					
			if (rs.next ())
			{
				result.put("saldo1", rs.getBigDecimal("saldo1"));
				result.put("saldo2", rs.getBigDecimal("saldo2"));
			}
		}
		catch (SQLException e)
		{
			log.log(Level.SEVERE, "product", e);
		}
		finally
		{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}	
		
		return result;
		
	}
	
	private String getSqlSaldo(int account_ID, int C_BPartner_ID){
		
		StringBuffer sql = new StringBuffer();
		
		sql.append(" WITH main AS (SELECT ad_client_id,ad_org_id,account_id,c_bpartner_id as subkonto1_id, \n "+
				   " SUM(amtacctdr) as dr1, SUM(amtacctcr) as cr1,0::numeric as dr2, 0::numeric as cr2  \n "+
				   " FROM v_fi_fact_acct_with_subconto \n "+
				   " WHERE dateacct < DATE_TRUNC('MONTH', "+DB.TO_DATE(dateFrom, true)+") AND account_id = "+account_ID+" AND c_bpartner_id="+C_BPartner_ID+""+
				   " GROUP BY ad_client_id,ad_org_id,account_id,c_bpartner_id UNION ALL \n "+  
				   " SELECT ad_client_id,ad_org_id,account_id,c_bpartner_id as subkonto1_id, \n "+
				   " 0::numeric as dr1,0::numeric as cr1,SUM(amtacctdr) as dr2, SUM(amtacctcr) as cr2 \n "+
				   " FROM v_fi_fact_acct_with_subconto \n "+ 
				   " WHERE dateacct BETWEEN DATE_TRUNC('MONTH', "+DB.TO_DATE(dateFrom, true)+") AND DATE_TRUNC('MONTH', "+DB.TO_DATE(dateFrom, true)+")+interval '1 month'-interval '1 second' AND account_id = "+account_ID+" AND c_bpartner_id="+C_BPartner_ID+" \n "+
				   " GROUP BY ad_client_id,ad_org_id,account_id,c_bpartner_id) \n"+
				   " SELECT m.ad_client_id, m.ad_org_id, m.account_id, m.subkonto1_id, c.value as accvalue, bp.value as subvalue, bp.name as subname, \n"+
				   " adempiere.func1(SUM(dr1),SUM(cr1),'cr')-adempiere.func1(SUM(dr1),SUM(cr1),'dr') as saldo1, \n"+  
				   " adempiere.func1((SUM(dr1)+SUM(dr2)),(SUM(cr1)+SUM(cr2)),'cr')-adempiere.func1((SUM(dr1)+SUM(dr2)),(SUM(cr1)+SUM(cr2)),'dr') as saldo2  \n "+
				   "   FROM main m \n "+				   
				   " LEFT JOIN c_bpartner bp ON(m.subkonto1_id = bp.c_bpartner_id) \n " +
				   " LEFT JOIN c_elementvalue c ON(m.account_id = c.c_elementvalue_id) \n " +
				   " WHERE m.ad_client_id = "+ Env.getAD_Client_ID(m_ctx) +" AND m.ad_org_id = "+Env.getAD_Org_ID(m_ctx) +" AND bp.IsEmployee = 'Y' and bp.c_bpartner_id="+C_BPartner_ID+"\n "+
				   " GROUP BY m.ad_client_id,m.ad_org_id,m.account_id,m.subkonto1_id,c.value,bp.value,bp.name \n" +
				   " ORDER BY bp.name  ");
		
		return sql.toString();
	}
	
	public class PayrollList {
		
		private int BPartner_ID;
		private String Name;
		private int Department_ID;
		private String DepName;
		private String JobName;
		private double salary;
		private int workDays;
		private int workHours;
		private double begBalance;
		private String method;
		// Accrued amount
		private double totalAccrued;
		private double compensationTrip;
		private double compensationVacation;
		private double materialAid;
		private double aidVacation;
		private double medicareAccrued;
		private double amountInverse;
		private double amountDirect;
		private double amountIllness;
		private double amountVacation;
		private double amountBonus;
		// Deduction amount
		private double totalDeduction;
		private double amountLoan;
		private double IPN;
		private double OPV;
		private double CNALOG;
		private double COTCH;
		private double inter;
		private double checkOff;
		private double medicareDeduction;
		private double imprest;
		//
		private double totalAmount;
		private double endBalance;
		private double taxExemption;
		
		public String getName() {
			return Name;
		}
		public void setName(String name) {
			Name = name;
		}
		public int getDepartment_ID() {
			return Department_ID;
		}
		public String getDepName() {
			return DepName;
		}
		public void setDepName(String depName) {
			DepName = depName;
		}
		public void setDepartment_ID(int department_ID) {
			Department_ID = department_ID;
		}
		public String getJobName() {
			return JobName;
		}
		public void setJobName(String jobName) {
			JobName = jobName;
		}
		public int getBPartner_ID() {
			return BPartner_ID;
		}
		public void setBPartner_ID(int BPartner_ID) {
			this.BPartner_ID = BPartner_ID;
		}
		public double getSalary() {
			return salary;
		}
		public void setSalary(double salary) {
			this.salary = salary;
		}
		public int getWorkDays() {
			return workDays;
		}
		public void setWorkDays(int workDays) {
			this.workDays = workDays;
		}
		public int getWorkHours() {
			return workHours;
		}
		public void setWorkHours(int workHours) {
			this.workHours = workHours;
		}
		public double getBegBalance() {
			return begBalance;
		}
		public void setBegBalance(double begBalance) {
			this.begBalance = begBalance;
		}
		public String getMethod() {
			return method;
		}
		public void setMethod(String method) {
			this.method = method;
		}
		public double getTotalAccrued() {
			return totalAccrued;
		}
		public void setTotalAccrued(double totalAccrued) {
			this.totalAccrued = totalAccrued;
		}
		public double getCompensationTrip() {
			return compensationTrip;
		}
		public void setCompensationTrip(double compensationTrip) {
			this.compensationTrip = compensationTrip;
		}
		public double getCompensationVacation() {
			return compensationVacation;
		}
		public void setCompensationVacation(double compensationVacation) {
			this.compensationVacation = compensationVacation;
		}
		public double getMaterialAid() {
			return materialAid;
		}
		public void setMaterialAid(double materialAid) {
			this.materialAid = materialAid;
		}
		public double getAidVacation() {
			return aidVacation;
		}
		public void setAidVacation(double aidVacation) {
			this.aidVacation = aidVacation;
		}
		public double getMedicareAccrued() {
			return medicareAccrued;
		}
		public void setMedicareAccrued(double medicareAccrued) {
			this.medicareAccrued = medicareAccrued;
		}
		public double getAmountInverse() {
			return amountInverse;
		}
		public void setAmountInverse(double amountInverse) {
			this.amountInverse = amountInverse;
		}
		public double getAmountDirect() {
			return amountDirect;
		}
		public void setAmountDirect(double amountDirect) {
			this.amountDirect = amountDirect;
		}
		public double getAmountIllness() {
			return amountIllness;
		}
		public void setAmountIllness(double amountIllness) {
			this.amountIllness = amountIllness;
		}
		public double getAmountVacation() {
			return amountVacation;
		}
		public void setAmountVacation(double amountVacation) {
			this.amountVacation = amountVacation;
		}
		public double getAmountBonus() {
			return amountBonus;
		}
		public void setAmountBonus(double amountBonus) {
			this.amountBonus = amountBonus;
		}
		public double getTotalDeduction() {
			return totalDeduction;
		}
		public void setTotalDeduction(double totalDeduction) {
			this.totalDeduction = totalDeduction;
		}
		public double getAmountLoan() {
			return amountLoan;
		}
		public void setAmountLoan(double amountLoan) {
			this.amountLoan = amountLoan;
		}
		public double getIPN() {
			return IPN;
		}
		public void setIPN(double iPN) {
			IPN = iPN;
		}
		public double getOPV() {
			return OPV;
		}
		public void setOPV(double oPV) {
			OPV = oPV;
		}
		public double getCNALOG() {
			return CNALOG;
		}
		public void setCNALOG(double cNALOG) {
			CNALOG = cNALOG;
		}
		public double getCOTCH() {
			return COTCH;
		}
		public void setCOTCH(double cOTCH) {
			COTCH = cOTCH;
		}
		public double getInter() {
			return inter;
		}
		public void setInter(double inter) {
			this.inter = inter;
		}
		public double getCheckOff() {
			return checkOff;
		}
		public void setCheckOff(double checkOff) {
			this.checkOff = checkOff;
		}
		public double getMedicareDeduction() {
			return medicareDeduction;
		}
		public void setMedicareDeduction(double medicareDeduction) {
			this.medicareDeduction = medicareDeduction;
		}
		public double getImprest() {
			return imprest;
		}
		public void setImprest(double imprest) {
			this.imprest = imprest;
		}
		public double getTotalAmount() {
			return totalAmount;
		}
		public void setTotalAmount(double totalAmount) {
			this.totalAmount = totalAmount;
		}
		public double getEndBalance() {
			return endBalance;
		}
		public void setEndBalance(double endBalance) {
			this.endBalance = endBalance;
		}
		public double getTaxExemption() {
			return taxExemption;
		}
		public void setTaxExemption(double taxExemption) {
			this.taxExemption = taxExemption;
		}
	}

}
